#!/bin/bash


APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

#1.1.1 流量域会话粒度页面浏览最近1日汇总表
dws_traffic_session_page_view_1d = "
   insert overwrite table $APP.dws_traffic_session_page_view_1d partition(dt='$do_date')
    select
                    session_id,
                    mid_id,
                    brand,
                    model,
                    operate_system,
                    version_code,
                    channel,
                    sc,
                    source,
                    during_time_1d,
                    page_count_1d
    from
        (
            select
                    session_id,
                    mid_id,
                    brand,
                    model,
                    operate_system,
                    version_code,
                    channel,
                    sc,
                    dt,
                    sum(during_time) during_time_1d,
                    count(*) page_count_1d
            from $APP.dwd_traffic_page_view_inc
            where dt = '$do_date'
            group by session_id, mid_id, brand, model, operate_system, version_code, channel,sc,dt
    ) tpv left join $APP.ods_base_source_full bs
    on tpv.sc = bs.id and tpv.dt = bs.dt;
"
#1.1.3 流量域来源粒度买课用户最近1日汇总表

dws_traffic_source_ordersum_1d ="
    insert overwrite table $APP.dws_traffic_source_ordersum_1d partition(dt='$do_date')
    select sc,
        sum(nvl(order_1,0)) user_order_sum,
        count(1) user_sum
    from
    (
        select max(sc) sc,
            user_id
        from $APP.dwd_traffic_video_view_inc
        where dt = '$do_date'
        group by user_id
        )t1
    left join(
        select user_id,
            1 order_1
        from $APP.dwd_trade_order_detail_inc
        where dt = '$do_date'
        group by user_id
        )t2
    on t1.user_id = t2.user_id
    group by sc;
"
#1.2.6用户域年龄段粒度最近1日下单汇总表
dws_age_group_order_1d="
    insert into table $APP.dws_age_group_order_1d partition (dt='$do_date')
    select distinct t4.age_group
                , count(t4.oid) over (partition by t4.age_group) as orderct
    from (select t3.oid,
                t3.dt               dt,
                (case
                    when t3.age < 18 then '未成年'
                    when t3.age > 18 and t3.age > 40 then '青年'
                    when t3.age > 40 and t3.age < 60
                        then '中年'
                    else '老年' end) age_group
        from (select t2.age, t2.order_id oid, t2.user_id uid, t2.dt dt
                from (select ed.order_id order_id, t1.user_id, `if`(month < 0, year - 1, year) age, ed.dt dt
                    from (select id                                        user_id,
                                year(`current_date`()) - year(birthday)   year,
                                month(`current_date`()) - month(birthday) month
                            from $APP.dim_user_zip) t1
                            join $APP.dwd_trade_order_detail_inc ed on ed.user_id = t1.user_id) t2) t3) t4
                            where t4.dt='$do_date';
"

#1.3.1-1.3.3 交易域分类粒度课程订单最近1日汇总表
dws_course_order_category_1d="
    insert overwrite table $APP.dws_course_order_category_1d partition(dt='$do_date')
    select
            course_id,
            subject_id,
            category_id,
            count(1) order_count_1d,
            count(distinct user_id) order_person_count_1d,
            sum(final_amount) order_amounts_1d
    from
        (
        (
            select user_id,
                    course_id,
                    final_amount
            from $APP.dwd_trade_order_detail_inc
            where dt = '$do_date'
        ) tod
            left join
        (
            select id,
                    subject_id,
                    category_id
            from $APP.dim_course_full
            where dt = '$do_date'
            group by id, subject_id, category_id
        ) cou
        on tod.course_id = cou.id
        )
    group by course_id,subject_id,category_id;
"
#
dws_process_user_course_review_1d="
    insert overwrite table $APP.dws_process_user_course_review_1d partition(dt='$do_date')
    select
        course_id,
        review_stars,
        avgReview1,
        reviewNum1,
        rev1/reviewNum1 positiveRatings1
    from (
            (
                select
                        data.id d2,
                        avg(data.review_stars)         avgReview1,
                        count(distinct (data.user_id)) reviewNum1
                from ods_review_info_inc
                where dt = '$do_date'
                group by data.id
            ) ri1
                full outer join (
                select
                        data.id d1,
                        data.review_stars,
                        data.course_id course_id,

                        count(distinct (data.user_id)) rev1
                from ods_review_info_inc
                where dt = '$do_date'
                and data.review_stars = '5'
                group by data.course_id, data.review_stars,data.id
            ) ri2 on ri1.d2=ri2.d1
            );
"
#1.3.5-1.3.7-课程域课程粒度最近一日第一次播放人数
dws_course_course_videofirst_1d="
    insert overwrite table $APP.dws_course_course_videofirst_1d partition(dt='$do_date')
    select t2.course_id,
        t2.subject_id,
        t2.category_id,
        nvl(order_person_count,0) order_person_count,
        first_start_person_count
    from
        (
            select course_id,
                subject_id,
                category_id,
                count(distinct user_id) order_person_count
            from (
                (
                    select s.user_id, s.course_id
                    from $APP.dwd_trade_order_detail_inc dto
                    right join
                        (
                            select user_id,video_id,course_id
                            from $APP.dwd_traffic_video_view_inc dtvv
                            left join (
                                select id,course_id
                                from $APP.dim_video_full
                                where dt = '$do_date'
                                ) dv
                            on dtvv.video_id = dv.id
                            where dt = '$do_date' and is_new = "1"
                            group by user_id,video_id,course_id
                        ) s
                    on dto.user_id = s.user_id and dto.course_id = s.course_id
                    where dt = '$do_date'
                    group by s.user_id, s.course_id
                ) tod
                    left join
                (
                    select id,
                            subject_id,
                            category_id
                    from $APP.dim_course_full
                    where dt = '$do_date'
                    group by id, subject_id, category_id
                ) cou
                on tod.course_id = cou.id
                    )
            group by course_id, subject_id, category_id,user_id
        ) t1
        right join
        (
    select
    course_id,
    subject_id,
    category_id,
    count(distinct user_id) first_start_person_count
    from (
        (
            select user_id,
                    video_id,
                    course_id
            from
            (
            select user_id,video_id
                from $APP.dwd_traffic_video_view_inc
                where play_sec > 0 and dt = '$do_date' and is_new = "1"
                group by user_id, video_id
            ) vv
                left join
                (
                select id,
                        course_id
                from $APP.dim_video_full
                where dt = '$do_date'
                group by id,course_id
                )v
            on vv.video_id = v.id
        ) vc
            left join
        (
            select id,
                    subject_id,
                    category_id
            from $APP.dim_course_full
            where dt = '$do_date'
            group by id, subject_id, category_id
        ) cou

        on vc.course_id = cou.id

        )
    group by course_id, subject_id,category_id
    )t2
    on t1.course_id = t2.course_id;
"
#------------------------------1.4交易域省份粒度课程订单最近1日汇总表
dws_course_order_province_1d="
    insert overwrite table $APP.dws_course_order_province_1d partition(dt='$do_date')
    select
            province_id,
            province_name,
            area_code,
            iso_code,
            iso_3166_2,
            order_count_1d,
            order_person_count_1d,
            order_amounts_1d
    from
        (
        (
            select province_id,
                    count(1) order_count_1d,
                    count(distinct user_id) order_person_count_1d,
                    sum(final_amount) order_amounts_1d,
                    dt
            from $APP.dwd_trade_order_detail_inc
            where dt= '$do_date'
            group by province_id,dt
        ) tod
        left join $APP.dim_province_full dp
        on tod.province_id = dp.id and tod.dt = dp.dt
        );
"
# 1.5.1,2 考试域课程粒度平均分数
dws_exam_course_sum_1d="
    insert overwrite table $APP.dws_exam_course_sum_1d partition(dt='$do_date')
    select  t2.paper_id,
        course_id,
        score_sum,
        end_time_sum,
        user_count
    from (
            select course_id,
                    paper_id
            from $APP.dim_paper_full
            where dt = '$do_date'
            group by course_id, paper_id
        ) t1
    right join
        ( select
                paper_id,
                sum(score) score_sum,
                sum(duration_sec) end_time_sum,
                count(1)  user_count
            from (
                select paper_id,
                        max(score) score,
                        max(duration_sec) duration_sec
                from
                    $APP.dwd_interaction_exam_inc
                where dt = '$do_date'
                group by paper_id,user_id
                )t
            group by t.paper_id
        ) t2
    on t1.paper_id = t2.paper_id;
    "
# 1.5.3 考试域课程粒度平均分数
exam_user_paper_statistics_distribution_1d="
    insert overwrite table $APP.dws_exam_score_distribution_1d partition(dt='$do_date')
    select
        paper_id,
        max(score),
        sum(case when diei.score between 90 and 100 then 1 else 0 end) as scoredistributionwangzhe,
        sum(case when diei.score between 80 and 89 then 1 else 0 end)  as scoredistributionyouxiu,
        sum(case when diei.score between 70 and 79 then 1 else 0 end)  as scoredistributionlianghao,
        sum(case when diei.score between 60 and 69 then 1 else 0 end)  as scoredistributionjige,
        sum(case when diei.score<60 then 1 else 0 end)  as scoredistributionbujige
    from (
        select  paper_id,
            max(score) score
        from $APP.dwd_interaction_exam_inc
        group by user_id,paper_id
            ) diei
    group by paper_id;
"

#1.5.4 考试域题目粒度统计
dws_exam_question_sum_1d="
    insert overwrite table $APP.dws_exam_question_sum_1d partition(dt='$do_date')
    select
        question_id,
        sum(if(is_correct = '1', 1, 0)) correct_sum,
        count(1) question_sum
    from $APP.dwd_interaction_exam_inc
    group by question_id;
"
#1.6 播放域课程粒度最近一日播放次数
dws_play_course_playvideo_1d="
        insert overwrite table $APP.dws_play_course_playvideo_1d partition(dt='$do_date')
    select course_id,
        chapter_id,
        sum(playcount),
        sum(play),
        count(distinct user_id)
    from
    (
    select user_id,
            video_id,
            sum(play_sec) play,
            count(1)    playcount
    from $APP.dwd_traffic_video_view_inc
    where dt = '$do_date'
    group by user_id, video_id
    )t1
    left join
    (
    select id,chapter_id,course_id
    from $APP.dim_video_full
        where dt = '$do_date'
    )t2
    on t1.video_id = t2.id
    group by chapter_id,course_id;
"
#1.7 播放域课程粒度最近一日用户播放进度
dws_play_course_userplayend_1d="
        insert overwrite table $APP.dws_play_course_userplayend_1d partition(dt='$do_date')
    select user_id,
        video_id,
        course_id,
        chapter_id,
        video_size,
        video_play_sec,
        video_position_sec
    from (
            select user_id,
                    video_id,
                    sum(play_sec)     video_play_sec,
                    max(position_sec) video_position_sec
            from $APP.dwd_traffic_video_view_inc
            where dt = '$do_date'
            group by user_id, video_id
        )t1

    left join
        (
            select id,
                course_id,
                chapter_id,
                video_size
            from $APP.dim_video_full
            where dt = '$do_date'
        )t2
    on  t1.video_id = t2.id;
"
case $1 in
"dws_traffic_session_page_view_1d")
    hive -e "$dws_traffic_session_page_view_1d"
;;
"dws_traffic_source_ordersum_1d")
    hive -e "$dws_traffic_source_ordersum_1d"
;;
"dws_age_group_order_1d")
    hive -e "$dws_age_group_order_1d"
;;
"dws_course_order_category_1d")
    hive -e "$dws_course_order_category_1d"
;;
"dws_process_user_course_review_1d")
    hive -e "$dws_process_user_course_review_1d"
;;
"dws_course_course_videofirst_1d")
    hive -e "$dws_course_course_videofirst_1d"
;;
"dws_course_order_province_1d")
    hive -e "$dws_course_order_province_1d"
;;
"dws_exam_course_sum_1d")
    hive -e "$dws_exam_course_sum_1d"
;;
"dws_exam_score_distribution_1d")
    hive -e "$dws_exam_score_distribution_1d"
;;
"dws_exam_question_sum_1d")
    hive -e "$dws_exam_question_sum_1d"
;;
"dws_play_course_playvideo_1d")
    hive -e "$dws_play_course_playvideo_1d"
;;
"dws_play_course_userplayend_1d")
    hive -e "$dws_play_course_userplayend_1d"
;;
"all")
    hive -e "$dws_traffic_session_page_view_1d$dws_traffic_source_ordersum_1d$dws_age_group_order_1d$dws_course_order_category_1d$dws_process_user_course_review_1d$dws_course_course_videofirst_1d$dws_course_order_province_1d$dws_exam_course_sum_1d$dws_exam_score_distribution_1d$dws_exam_question_sum_1d$dws_play_course_playvideo_1d$dws_play_course_userplayend_1d"
;;
esac